package com.cttic.erp.common.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.cttic.erp.common.exception.SystemException;

public class ExcelUtil {

	public static void generator(String remoteFilePath, String rometeFileName,
			String[] tableHeads, List<String[]> values) {
		try {
			// 创建Excel文本
			WritableWorkbook wbook = Workbook.createWorkbook(new File(
					remoteFilePath + rometeFileName));
			// sheet名称
			WritableSheet wsheet = wbook.createSheet("Sheet1", 0);
			// 生成表头
			for (int col = 0; col < tableHeads.length; col++) {
				wsheet.addCell(new Label(col, 0, tableHeads[col]));
			}
			// 生成内容
			for (int row = 1; row <= values.size(); row++) {
				for (int col = 0; col < tableHeads.length; col++) {
					wsheet.addCell(new Label(col, row, values.get(row - 1)[col]));
				}
			}
			// 写入文件
			wbook.write();
			wbook.close();
		} catch (Exception e) {
			throw new SystemException("生成Excel文件出错", e);
		}
	}

	public static void download(HttpServletResponse response,
			String localFileName, String remoteFilePath, String rometeFileName) {
		try {
			response.setContentType(remoteFilePath);
			FileInputStream fileContext = new FileInputStream(remoteFilePath
					+ rometeFileName);
			BufferedInputStream inStream = new BufferedInputStream(fileContext);
			response.reset();
			response.setContentType("application/x-msdownload");
			response.setHeader(
					"Content-Disposition",
					"attachment; filename="
							+ new String(localFileName.getBytes("gb2312"),
									"ISO-8859-1"));
			OutputStream sOut = response.getOutputStream();
			byte[] b = new byte[1024];
			int len = 0;
			while ((len = inStream.read(b)) != -1) {
				sOut.write(b, 0, len);
			}
			sOut.close();
			inStream.close();
			fileContext.close();
			File file = new File(remoteFilePath + rometeFileName);
			file.delete();
		} catch (Exception e) {
			throw new SystemException("下载Excel文件出错", e);
		}
	}

	public static Sheet importByTemplate(String fileName) {
		Workbook workbook = null;
		Sheet sheet = null;
		try {
			File is = new File(fileName);
			// 获取工作簿对象
			workbook = Workbook.getWorkbook(is);
			if (workbook != null) {
				Sheet[] sheets = workbook.getSheets();
				if (sheets != null && sheets.length != 0) {
					sheet = sheets[0];
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭
			if (null != workbook) {
//				workbook.close();
			}
		}
		return sheet;
	}

	public static WritableSheet exportByTemplate(String templateName, String fileName) {
		WritableWorkbook writableWorkbook = null;
		WritableSheet writableSheet = null;
		FileOutputStream fileOutputStream = null;
		// 获取要读取的EXCEL表格模板
		File templateFile = new File(templateName);
		// 写入到新的表格里
		File newFile = new File(fileName);
		try {
			// 创建新文件
			newFile.createNewFile();
			fileOutputStream = new FileOutputStream(newFile);
			// 获取工作簿对象
			Workbook workbook = Workbook.getWorkbook(templateFile);
			// 创建可写入的工作簿对象
			writableWorkbook = Workbook.createWorkbook(fileOutputStream, workbook);
			// 根据工作表名获取WritableSheet对象
			writableSheet = writableWorkbook.getSheet("Sheet1");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭流
			try {
				writableWorkbook.close();
				fileOutputStream.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return writableSheet;
	}
	
	public static void main(String[] args) throws IOException {
		WritableWorkbook writableWorkbook = null;
		WritableSheet writableSheet = null;
		FileOutputStream fileOutputStream = null;
		// 获取要读取的EXCEL表格模板
		String filePath = ExcelUtil.class.getResource("/template/excel/templete.xls").getFile();
		File templateFile = new File(filePath);
		try {
			// 获取工作簿对象
			Workbook workbook = Workbook.getWorkbook(templateFile);
			// 创建可写入的工作簿对象
			writableWorkbook = Workbook.createWorkbook(fileOutputStream, workbook);
			// 根据工作表名获取WritableSheet对象
			writableSheet = writableWorkbook.getSheet("Sheet1");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭流
			try {
				writableWorkbook.close();
				fileOutputStream.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}
